﻿set ansi_padding, ansi_warnings, concat_null_yields_null, arithabort, quoted_identifier, ansi_nulls on
go

if exists (select * from tempdb..sysobjects where id=object_id('tempdb..#tmpProperties')) drop table #tmpProperties
go

create table #tmpProperties ( name nvarchar(255), value nvarchar(255) )
go

insert into #tmpProperties values('script', 'update.0007.sql')
insert into #tmpProperties values('error', '0')

set xact_abort on
go

set transaction isolation level serializable
go

begin transaction
go

set ansi_nulls on
set quoted_identifier on
go
/* BEGIN UPDATE SCRIPT */


/* Add  UCID, FROM_ADDRESS, REASON columns */

alter table dbo.Calls add
	UCID varchar(50),
	FROM_ADDRESS varchar(50),
	REASON_ID uniqueidentifier,
	HEAD_NUMBER varchar(50)
go

create table dbo.REASONS (
	ID uniqueidentifier,
	NAME nvarchar(255),
	SORT_ORDER int
)
go

ALTER View [dbo].[vwCALLS]
as
select CALLS.ID
     , CALLS.NAME
     , CALLS.DURATION_HOURS
     , CALLS.DURATION_MINUTES
     , dbo.fnViewDateTime(CALLS.DATE_START, CALLS.TIME_START) as DATE_START
     , CALLS.DATE_END
     , CALLS.PARENT_TYPE
     , CALLS.STATUS
     , CALLS.DIRECTION
     , CALLS.ASSIGNED_USER_ID
     , CALLS.REMINDER_TIME
     , CALLS.PARENT_ID
     , vwPARENTS.PARENT_NAME
     , vwPARENTS.PARENT_ASSIGNED_USER_ID
     , CALLS.DATE_ENTERED
     , CALLS.DATE_MODIFIED
     , CALLS.DATE_MODIFIED_UTC
     , CALLS.DESCRIPTION
     , TEAMS.ID                    as TEAM_ID
     , TEAMS.NAME                  as TEAM_NAME
     , USERS_ASSIGNED.USER_NAME    as ASSIGNED_TO
     , USERS_CREATED_BY.USER_NAME  as CREATED_BY
     , USERS_MODIFIED_BY.USER_NAME as MODIFIED_BY
     , CALLS.CREATED_BY            as CREATED_BY_ID
     , CALLS.MODIFIED_USER_ID
     , TEAM_SETS.ID                as TEAM_SET_ID
     , TEAM_SETS.TEAM_SET_NAME     as TEAM_SET_NAME
     , TEAM_SETS.TEAM_SET_LIST     as TEAM_SET_LIST
     , dbo.fnFullName(USERS_ASSIGNED.FIRST_NAME   , USERS_ASSIGNED.LAST_NAME   ) as ASSIGNED_TO_NAME
     , dbo.fnFullName(USERS_CREATED_BY.FIRST_NAME , USERS_CREATED_BY.LAST_NAME ) as CREATED_BY_NAME
     , dbo.fnFullName(USERS_MODIFIED_BY.FIRST_NAME, USERS_MODIFIED_BY.LAST_NAME) as MODIFIED_BY_NAME
     , CALLS_CSTM.*
	 , CALLS.UCID
	 , CALLS.FROM_ADDRESS
	 , CALLS.REASON_ID
	 , CALLS.HEAD_NUMBER
	 , REASONS.NAME					as REASON_NAME
  from            CALLS
  left outer join REASONS
               on REASONS.ID			= CALLS.REASON_ID
  left outer join vwPARENTS
               on vwPARENTS.PARENT_ID    = CALLS.PARENT_ID
  left outer join TEAMS
               on TEAMS.ID               = CALLS.TEAM_ID
              and TEAMS.DELETED          = 0
  left outer join TEAM_SETS
               on TEAM_SETS.ID           = CALLS.TEAM_SET_ID
              and TEAM_SETS.DELETED      = 0
  left outer join USERS                    USERS_ASSIGNED
               on USERS_ASSIGNED.ID      = CALLS.ASSIGNED_USER_ID
  left outer join USERS                    USERS_CREATED_BY
               on USERS_CREATED_BY.ID    = CALLS.CREATED_BY
  left outer join USERS                    USERS_MODIFIED_BY
               on USERS_MODIFIED_BY.ID   = CALLS.MODIFIED_USER_ID
  left outer join CALLS_CSTM
               on CALLS_CSTM.ID_C        = CALLS.ID
 where CALLS.DELETED = 0
GO


/* END UPDATE SCRIPT */
go
if @@error<>0 and @@trancount>0 rollback transaction
go

if @@trancount=0 begin
	update #tmpProperties set value = 1 where name = 'error'
	begin transaction
end
go

if (select top 1 value from #tmpProperties where name = 'error') <> '0' rollback transaction
go

if @@trancount > 0 begin
	declare @@scriptName nvarchar(255)
	select top 1 @@scriptName = value from #tmpProperties where name = 'script'

	if dbo.scriptExisted(@@scriptName) = 1 begin
		rollback transaction
		print @@scriptName + ' already executes'
	end
	else begin
		exec dbo.updateScript @@scriptName
		commit transaction
		print 'The database update succeeded'
	end
END
else begin
	print 'The database update failed'
end
go

drop table #tmpProperties
go
